capture log close
clear all
set maxvar 30000
set more off
pause off
# delimit;

local crime "";
local output "";

/* 
Table B18
RD and IV estimates of criminal justice outcomes (18-year-old plus siblings)
*/


/* ********************** */
/* **** PREPARE DATA **** */
/* ********************** */

use "`crime'/prworacombsib_crime_summstats.dta", clear ;

keep if label == "prec_rd_mean" ;

local pre_all "a" ;


preserve ;

		keep	`pre_all'any_crime2char* `pre_all'any_crime2genr* 
				`pre_all'any_crime2nogn*  
				
				`pre_all'any_crime2prbg* `pre_all'any_crime2prtf* 
				`pre_all'any_crime2prfd* 
				`pre_all'any_crime2vrob* `pre_all'any_crime2dgsl* 
				`pre_all'any_crime2vcps* 
				
				`pre_all'any_crime2nitp* `pre_all'any_crime2vnrb*
				`pre_all'any_crime2nidp* `pre_all'any_crime2nidu* 
			
				`pre_all'any_crime2ipp* `pre_all'any_crime2sipp
				`pre_all'any_crime2ahinc* `pre_all'any_crime2shinc*
				;

xpose, clear varname ;

rename v1 mean ;
rename _varname variables ;
order variables mean ;

replace variables = subinstr(variables,"_1997_2017", "", .) ;
replace variables = subinstr(variables,"`pre_all'any_crime2", "", .) ;
replace variables = subinstr(variables,"`pre_all'any_", "", .) ;
		
tempfile all_mean ;
save `all_mean' ;

restore ;



clear;


foreach method in rd iv {;
		use "`crime'/prworacombsib_bw264_crime_poly1allcov_`method'_all.dta" ;

		#delim;
		gen any_crime2 = strpos(variables, "any_crime2") ;

		keep if any_crime2 == 1 ;
			
		if "`method'" == "rd" {;

			keep variables n_post sen_post N ;
			
		};
			
		else {;
	
			keep if any_crime2 == 1  ;			
			keep variables n_age18unfav sen_age18unfav N;
		};

		replace variables = subinstr(variables,"_1997_2017", "", .) ;
		replace variables = subinstr(variables,"any_crime2", "", .) ;
		replace variables = subinstr(variables,"any_", "", .) ;

		#delim;

		if "`method'" == "rd" {;
			
		local crime "char genr nogn prbg prtf prfd vrob dgsl
						 vcps vnrb acvn nitp nidp nidu ipp sipp 
						 ahinc shinc" ;

		};
			
		else {;
			
				local crime "char genr nogn prbg prtf prfd vrob dgsl vnrb acvn
							 vcps nitp nidp nidu ipp sipp ahinc shinc" ;

		};
						 
		gen keep = strpos(variables, "char") ;

		foreach var in `crime' {;

		replace keep = strpos(variables, "`var'") if keep == 0;

		};

		drop if keep == 0 ;
		drop keep ;


		merge 1:1 variables using `all_mean' ;
		drop _merge ;


		gen flag_int = strpos(variables, "t") ;

		gen length = length(variables) ;
		replace flag_int = 1 if variables == "shinc" | variables == "sipp" ;		
		replace flag_int = 1 if flag_int != 0 & length == 5;
		replace flag_int = 0 if flag_int != 1 ;
			
		drop if inlist(variables,"acvnt","cipp","ippt","aipp","acvn");

		replace variables = "Any charge" if variables == "char" | variables == "chart" ;
		replace variables = "Incarcerated (annual likelihood/days)" if variables == "ahinc" | variables == "shinc" ;
		replace variables = "Incarceration/parole/probation (likelihood/days)" if variables == "ipp" | variables == "sipp" ; 
			
		replace variables = "Total (income generating)" if variables == "genr" | variables == "genrt" ;	
		replace variables = "Burglary" if variables == "prbg" | variables == "prbgt" ;
		replace variables = "Theft" if variables == "prtf" | variables == "prtft" ;
		replace variables = "Fraud/forgery" if variables == "prfd" | variables == "prfdt" ;
		replace variables = "Robbery" if variables == "vrob" | variables == "vrobt" ;
		replace variables = "Drug distribution" if variables == "dgsl" | variables == "dgslt" ;
		replace variables = "Prostitution" if variables == "vcps" | variables == "vcpst" ;
		
		replace variables = "Total (non-income generating)" if variables == "nogn" | variables == "nognt" ;	
		replace variables = "Non-robbery violent" if variables == "vnrb"  | variables == "vnrbt" ;
		replace variables = "Criminal trespass" if variables == "nitp" | variables == "nitpt" ;
		replace variables = "Drug possession or paraphernalia" if variables == "nidp" | variables == "nidpt" ;
		replace variables = "Driving under the influence" if variables == "nidu" | variables == "nidut" ;
			

		sort variables flag_int ;

		if "`method'" == "rd" {;

			rename n_post pt_est_rd ;
			rename sen_post se_rd ;
			gen star_rd = 2 * (normal(-abs(pt_est_rd/se_rd))) ;
			gen sta_sign_rd = "" ;
			replace sta_sign_rd = "*" if star_rd <= 0.1 & star_rd > 0.05 ;
			replace sta_sign_rd = "**" if star_rd <= 0.05 & star_rd > 0.01 ;
			replace sta_sign_rd = "***" if star_rd <= 0.01 ;
			drop star_rd ;
		};
			
		else {;
				
			rename n_age18unfav pt_est_iv ;
			rename sen_age18unfav se_iv ;
			gen star_iv = 2 * (normal(-abs(pt_est_iv/se_iv))) ;
			gen sta_sign_iv = "" ;
			replace sta_sign_iv = "*" if star_iv <= 0.1 & star_iv > 0.05 ;
			replace sta_sign_iv = "**" if star_iv <= 0.05 & star_iv > 0.01 ;
			replace sta_sign_iv = "***" if star_iv <= 0.01 ;
			drop star_iv ;
		};
			
					
		order variables pt_est* se* sta_sign* mean N flag_int;
				
		tostring flag_int, replace ;
			
		replace flag_int = "_in" if flag_int == "1" ;
		replace flag_int = "_ex" if flag_int == "0" ;			

		drop length ;
			
		reshape wide pt_est* se* sta_sign* mean N, i(variables) j(flag_int) string ;

		tostring se*, replace ;
		replace se_`method'_ex = "(" + se_`method'_ex + ")" ;
		replace se_`method'_in = "(" + se_`method'_in + ")" ;
			
		tempfile all_`method'_all ;
		save `all_`method'_all' ;
			
}; 
		
use `all_rd_all', clear ;
		
merge 1:1 variables using `all_iv_all' ;
drop _merge ;
		
		
order variables pt_est_rd_ex sta_sign_rd_ex se_rd_ex pt_est_iv_ex sta_sign_iv_ex se_iv_ex mean_ex N_ex
				pt_est_rd_in sta_sign_rd_in se_rd_in pt_est_iv_in sta_sign_iv_in se_iv_in mean_in N_in ;
		
		
gen order = 1 if variables == "Any charge" ;
replace order = 2 if variables == "Incarcerated (annual likelihood/days)" ;
replace order = 3 if variables == "Incarceration/parole/probation (likelihood/days)" ;
replace order = 4 if variables == "Total (income generating)" ;
replace order = 5 if variables == "Burglary" ;
replace order = 6 if variables == "Theft" ;
replace order = 7 if variables == "Fraud/forgery" ;
replace order = 8 if variables == "Robbery" ;
replace order = 9 if variables == "Drug distribution" ;
replace order = 10 if variables == "Prostitution" ;
replace order = 11 if variables == "Total (non-income generating)" ;
replace order = 12 if variables == "Non-robbery violent" ;
replace order = 14 if variables == "Criminal trespass" ;
replace order = 15 if variables == "Drug possession or paraphernalia" ;
replace order = 16 if variables == "Driving under the influence" ;
			
sort order ;
		
drop order ;

export excel using "`output'/tabB18.xls", firstrow(variables) ;

